﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.IO;
using System.Linq;
using System.Text;

namespace MachineBuildingSystem
{
    public class ParadoxHelper
    {
        private string m_ConnectString = "";
        private static ParadoxHelper Instance = null;

        private ParadoxHelper()
        {
            m_ConnectString = ConfigurationManager.AppSettings["ParadoxConnectionStr"];
        }

        public static ParadoxHelper CreateInstance()
        {
            if (Instance == null)
            {
                Instance = new ParadoxHelper();
            }
            return Instance;
        }

        public bool DoSqlExecute(string path, string[] sqlArray)
        {
            OdbcConnection conn = null;
            OdbcCommand command = null;

            try
            {
                conn = new OdbcConnection(CtreateNewConnectionStr(path));
                conn.Open();

                command = new OdbcCommand();
                command.Connection = conn;
                for (int i = 0; i < sqlArray.Length; i++)
                {
                    command.CommandText = sqlArray[i];
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                string str = "批量执行异常：" + ex.Message;
                LogHelper.error(str);
                throw;
            }
            finally
            {
                conn.Close();
            }
            return true;
        }

        private string CtreateNewConnectionStr(string path)
        {
            string connectString = "";
            string[] strArr = m_ConnectString.Split(';');
            string Driver = "";
            string DriverID = "";
            string Fil = "";
            string DefaultDir = "";
            string Dbq = "";
            string CollatingSequence = "";
            foreach (string str in strArr)
            {
                if (str.Contains("Driver="))
                    Driver = str;
                if (str.Contains("DriverID="))
                    DriverID = str;
                if (str.Contains("Fil="))
                    Fil = str;
                if (str.Contains("DefaultDir="))
                    DefaultDir = path == "" ? str : str + path + "\\";
                if (str.Contains("Dbq="))
                    Dbq = path == "" ? str : str + path + "\\";
                if (str.Contains("CollatingSequence="))
                    CollatingSequence = str;
            }
            connectString = Driver + ";" + DriverID + ";" + Fil + ";"
                + DefaultDir + ";" + Dbq + ";" + CollatingSequence;
            return connectString;
        }

        public DataTable DoSqlSelect(string path, string[] sqlArray)
        {
            DataTable retDT = new DataTable();

            OdbcConnection conn = null;
            OdbcCommand command = null;
            try
            {
                conn = new OdbcConnection(CtreateNewConnectionStr(path));
                conn.Open();

                command = new OdbcCommand();
                command.Connection = conn;
                List<DataTable> tempList = new List<DataTable>();
                for (int i = 0; i < sqlArray.Length; i++)
                {
                    string[] path1 = sqlArray[i].Split(new string[] { "from" }, StringSplitOptions.RemoveEmptyEntries);
                    string[] path2 = path1[1].Split(new string[] { "where" }, StringSplitOptions.RemoveEmptyEntries);
                    string fileName = path2[0].Trim();
                    string filePath = Path.Combine(conn.Database, fileName);
                    //判断表名文件是否存在
                    if (!File.Exists(filePath))
                        continue;
                    command.CommandText = sqlArray[i];
                    DataSet ds = new DataSet();
                    OdbcDataAdapter sda = new OdbcDataAdapter(command);
                    sda.Fill(ds);
                    tempList.Add(ds.Tables[0]);
                }
                if (tempList.Count > 0)
                {
                    retDT = tempList[0].Clone();
                    foreach (DataTable tempDT in tempList)
                    {
                        foreach (DataRow row in tempDT.Rows)
                        {
                            retDT.Rows.Add(row.ItemArray);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string sqlStr = "";
                foreach (string sql in sqlArray)
                {
                    sqlStr += sql + ";";
                }
                string str = "执行SQL：" + sqlStr.TrimEnd(';') + "，数据错误：" + ex.Message;
                LogHelper.error(str);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return retDT;
        }
    }
}
